人-货-场 指标体系建设 之 SQL案例分析

点击上方 "蓝色"关注, “星标”一起成长

1
用户指标体系（人）

基础指标体系 + RFM模型分析

1.1 基础指标（uv/pv/留存率）

表结构

1.1.1  搭建数据表，读取数据并进行预处理：

此处需注意日期的格式，年-月-日 时间显示是12或24位进制，24进制显示%H，12小时进制显示%h。

1.1.2  根据数据，计算uv、pv以及流量深度，拆解问题步骤如下：

第一步：用behavior=1（用户行为为1记录为用户浏览行为），按照日期进行分组统计，计算pv值；

第一步：统计user_id的数量，按照日期进行分组统计，注意去重，计算uv值；

第三步：浏览深度计算方式为 pv/uv。

结果显示

1.1.3  留存率统计之前，先计算出当日活跃用户数以及1-7日，15日和30日留存数，问题拆解步骤如下：

第一步：将用户表作自连接，右表日期>=左表；

第二步：计算当日活跃用户数，注意去重；

第三步：计算1-7日，15日和30日留存数，注意去重；

第四部：利用视图进行封装，方便后续留存率计算。

结果显示

1.1.4  根据之前得出的留存数，计算留存率：

此处使用cast转换函数，用decimal精确数字长度（使用round函数也可）

结果显示

1.2 RFM模型

本次数据中通过最近消费（R）和消费频率（F）建⽴RFM模型

重要⾼价值客户：指最近⼀次消费较近⽽且消费频率较⾼的客户；

重要唤回客户：指最近⼀次消费较远且消费频率较⾼的客户；

重要深耕客户：指最近⼀次消费较近且消费频率较低的客户；

重要挽留客户：指最近⼀次消费较远且消费频率较低的客户；

1.2.1 建立R指标，根据每个用户的最近一次购买时间，给出相应的评分

第一步：获取每个用户的最近购买时间，建立视图；

第二步：计算每个用户最近购买时间距离2019-12-18相差几天，根据相差天数给予一定的评分（<=2 5;<=4 4;<=6 3; <=8 2; 其他 1），建立视图。

结果显示

1.2.2 建立F指标，求出每个用户购买次数，给出相应的评分

第一步：求出各个用户的购买次数，建立视图；

第二步：根据购买次数给予一定的评分（<=2 1; <=4 2;<=6 3,<=8 4; 其他 5）

结果显示

1.2.3 整合数据

第一步：算出R指标和F指标的均值；

第二步：拿到每个人的R指标和F指标（两表关联），与相应均值对比。

R指标和F指标的均值

每个人的指标与均值对比

结果显示

2
商品指标体系（货）

2.1 商品的点击量、收藏量、加购量、购买次数、购买转化

各种指标的计算也可使用 count(if(behavior_type=*,user_id,null)) 来计算

结果显示

2.2 对应商品品类的点击量、加购量、购买次数、购买转化

只需要将上述语句中的item_id替换为item_category。

3
平台指标体系（场）

3.1 行为指标

平台当日的点击次数、收藏次数、加购次数、购买次数、购买转化，与上述情况也一 样，只需要将语句中的item_category替换为dates。

3.2 行为路径分析

第一步：用户行为拼接准备，使用偏移函数和窗口函数将行为拆分为5步，使用rank函数根据时间的倒序进行排序，筛选出排行为1，behavior_type=2的数据，创建视图；

第二步：拼接行为路径；

第三步：针对行为路径进行分组count统计。

结果显示

4
 结论 

4.1 用户分析

UV周环比对比：日常周环比数据大多数大于0，说明用户数量呈现上升趋势，当UV或UV周环比出现数据异常，需要注意：

内部问题：产品BUG（⽹站bug）、策略问题（周年庆活动结束了）、营销问题（代⾔⼈换了）等；

外部问题：竞品活动问题（其他平台⼤酬宾），政治环境问题（进⼝商品限制），舆情⼝碑问题（平台商品爆出质量问题）等；

4.2 用户精细化运营

通过RFM模型中的⽤户最近⼀次购买时间、⽤户消费频次分析，分拆得到以下重要⽤户。可以在后续精细化运营场景中直接使⽤细分⽤户，做差异化运营：

对⾼价值客户做VIP服务设计，增加⽤户粘性同时通过设计优惠券提升客户消费；

对深耕客户做⼴告、推送刺激，提升消费频次；

对挽留客户做优惠券、签到送礼策略，增加挽留⽤户粘性；

对唤回客户做定向⼴告、短信召回策略，尝试召回⽤户。

4.3 商品分析

通过商品/商品品类的点击量、收藏量、加购量、购买次数、购买转化，筛选出相对转化较低的产品/品类，是否为某些品类特性导致的转化率较低，比如说，非当季商品、非必需品、奢侈品等。

4.4 行为路径分析

通过用户行为路径分析，可以查看用户购买的行为中，是否某个行为点击率相对较低，比如用户大多选择直接购买的方式，而加入购物车/收藏行为相对较少，后续需要根据产品的加购/收藏功能结合更多数据做出优化方案。

作者：Cecilia_Liu

原文：jianshu.com/p/e8e6a57a275f

Respect ~

欢迎大家关注呀！

回复关键词：666 777 源码 中台 画像 资料 spark ck 就业指导等都可以领取各种资源！